ALTER      proc sp_update_balance_qty_in_inventory_lot
@lot_id bigint,
@balancee_qty int,
@cslit_id int,
@cslnt_qty_pull int,
@is_reduce_inventory int,
@is_confirm int,
@shipDate varchar(50),
@isUpdatedByVendor bit,
@date_code varchar(20)
As
begin
	declare @cslnt_qty_pull_existing_qty int
	declare @il_qty_balance_existing int
	declare @il_qty_in_existing int
	declare @final_balance_qty int
	declare @final_qty_in int

	declare @is_drop_shipment int
	declare @product_type varchar(20)
	declare @lot_entered_by bit

	SELECT @is_drop_shipment = CST.CST_IS_Drop_Shipment FROM Customer_Shipping_Table CST INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID WHERE CSLIT.CSLIT_ID = @cslit_id
	SELECT @product_type = ISNULL(P.P_Type, 'CHIP') FROM Product P INNER JOIN Customer_Open_Order_LineItem COOL_1 ON P.Product_ID = COOL_1.Product_ID INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON COOL_1.LineItem_ID = CSLIT.LineItem_ID WHERE CSLIT.CSLIT_ID = @cslit_id
	SELECT @lot_entered_by = ISNULL(Lot_Entered_By,1) FROM Lot_Table WHERE Lot_Id = @lot_id

	IF @date_code IS NOT NULL AND LEN(RTRIM(LTRIM(@date_code))) > 0
	BEGIN
		UPDATE LOT_TABLE SET LOT_DATE_CODE = @date_code WHERE LOT_ID = @lot_id
	END
	-- This codition is satisfied when ps is not dropship and when ps is generating for first time
	IF @is_drop_shipment = 1 OR @cslit_id = -1
	BEGIN
		
		select @cslnt_qty_pull_existing_qty = CSLNT_Qty_Pull from customer_shipping_lot_number_table where cslit_id = @cslit_id and lot_id = @lot_id

		if @cslnt_qty_pull_existing_qty is not null and @cslnt_qty_pull_existing_qty > 0
		begin
			update Inventory_Lot set IL_Qty_Balance = IL_Qty_Balance+@cslnt_qty_pull_existing_qty where Lot_ID = @lot_id
		end

		select @il_qty_balance_existing = IL_Qty_Balance from Inventory_lot where Lot_Id = @lot_id

		if @il_qty_balance_existing is null
		begin
			select @il_qty_balance_existing = qty_in from Lot_Table where Lot_Id = @lot_id
		end

		if @cslnt_qty_pull != 0
		begin
			if @cslnt_qty_pull <= @il_qty_balance_existing
			begin
				update Inventory_Lot set IL_Qty_Balance = @il_qty_balance_existing-@cslnt_qty_pull where Lot_ID = @lot_id
			end
		end

		IF ( ( @is_confirm = 0 ) AND ( @product_type <> 'CHIP' OR @lot_entered_by = 0 ) )
		BEGIN
			UPDATE Inventory_Lot SET IL_Qty_In = IL_Qty_In - @cslnt_qty_pull WHERE Lot_ID = @lot_id
			UPDATE LOT_TABLE SET Qty_In = Qty_In - @cslnt_qty_pull WHERE Lot_ID = @lot_id
		END

	END
	ELSE
	BEGIN
		IF @isUpdatedByVendor = 0 -- when Vendor is updating the inventory from vendor interface screen
		BEGIN
			select @cslnt_qty_pull_existing_qty = CSLNT_Qty_Pull from customer_shipping_lot_number_table where cslit_id = @cslit_id and lot_id = @lot_id

			if @cslnt_qty_pull_existing_qty is not null and @cslnt_qty_pull_existing_qty > 0
			begin
				UPDATE Inventory_Lot SET IL_Qty_Balance = IL_Qty_Balance+@cslnt_qty_pull_existing_qty where Lot_ID = @lot_id
			end

			select @il_qty_balance_existing = IL_Qty_Balance from Inventory_lot where Lot_Id = @lot_id

			if @il_qty_balance_existing is null
			begin
				select @il_qty_balance_existing = qty_in from Lot_Table where Lot_Id = @lot_id
			end
			if @cslnt_qty_pull <= @il_qty_balance_existing
			begin
				SET @final_balance_qty = @il_qty_balance_existing - @cslnt_qty_pull
			end
			else
			begin
				SET @final_balance_qty = 0
			end

			if @cslnt_qty_pull != 0
			begin
				UPDATE Inventory_Lot SET IL_Qty_Balance = @final_balance_qty where Lot_ID = @lot_id
			end
		END
		ELSE
		BEGIN
			IF ( @is_confirm = 1 AND @is_reduce_inventory = 0 )
			BEGIN
				SELECT @cslnt_qty_pull_existing_qty = CSLNT_Qty_Pull FROM customer_shipping_lot_number_table where cslit_id = @cslit_id and lot_id = @lot_id

				IF @cslnt_qty_pull_existing_qty is not null and @cslnt_qty_pull_existing_qty > 0
				BEGIN
					UPDATE Inventory_Lot SET IL_Qty_Balance = IL_Qty_Balance+@cslnt_qty_pull_existing_qty where Lot_ID = @lot_id
				END

				SELECT @il_qty_balance_existing = IL_Qty_Balance FROM Inventory_lot WHERE Lot_Id = @lot_id

				IF @il_qty_balance_existing is null
				BEGIN
					SELECT @il_qty_balance_existing = qty_in FROM Lot_Table WHERE Lot_Id = @lot_id
				END

				IF ( @cslnt_qty_pull <= @il_qty_balance_existing )
				BEGIN
					SET @final_balance_qty = @il_qty_balance_existing - @cslnt_qty_pull
				END
				ELSE
				BEGIN
					SET @final_balance_qty = 0
				END
				UPDATE Inventory_Lot SET IL_Qty_Balance = @final_balance_qty WHERE Lot_ID = @lot_id
			END
			ELSE IF ( @is_confirm = 0 AND @is_reduce_inventory = 0 ) -- while confirming the Drop Shipment and with "Reduce balance" as checked
			BEGIN
				declare @lot_date_in datetime

				SELECT @lot_date_in = Date_In FROM Lot_Table WHERE Lot_Id = @lot_id

				--INSERT INTO INVENTORY_LOT_HISTORY ( Lot_Id, IL_Date_In, IL_Date_Out, TimeStamp ) VALUES ( @lot_id, @lot_date_in, @shipDate, CURRENT_TIMESTAMP )
				--INSERT INTO Lot_Table_History ( Lot_ID, ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Lot_Entered_By, Lot_Manual_Hold, Lot_Category, Wafer_Qty_In, Wafer_Qty_Out, Qty_In, Qty_Out, Date_In, Date_Out, Stage_Name, Stage_Position, Lot_Current_Status, Lot_Comments, Timestamp, Lot_Split, Lot_Merge, Good_Die_Qty, LOT_DATE_CODE, Lot_Drop_Out, SHOP_ORDER_NUMBER, PO_NUMBER ) 
				--	SELECT Lot_ID, ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Lot_Entered_By, Lot_Manual_Hold, Lot_Category, Wafer_Qty_In, Wafer_Qty_Out, Qty_In, Qty_Out, @lot_date_in, @shipDate, Stage_Name, Stage_Position, Lot_Current_Status, Lot_Comments, CURRENT_TIMESTAMP, Lot_Split, Lot_Merge, Good_Die_Qty, LOT_DATE_CODE, Lot_Drop_Out, SHOP_ORDER_NUMBER, PO_NUMBER FROM LOT_TABLE WHERE LOT_ID = @lot_id

				select @cslnt_qty_pull_existing_qty = CSLNT_Qty_Pull from customer_shipping_lot_number_table where cslit_id = @cslit_id and lot_id = @lot_id

				if @cslnt_qty_pull_existing_qty is not null and @cslnt_qty_pull_existing_qty > 0
				begin
					update Inventory_Lot set IL_Qty_Balance = IL_Qty_Balance+@cslnt_qty_pull_existing_qty where Lot_ID = @lot_id
				end

				SELECT @il_qty_in_existing = QTY_IN FROM LOT_TABLE WHERE LOT_ID = @lot_id
				SELECT @il_qty_balance_existing = IL_Qty_Balance FROM Inventory_lot WHERE Lot_Id = @lot_id

				IF @il_qty_balance_existing IS NULL
				BEGIN
					SELECT @il_qty_balance_existing = QTY_IN FROM LOT_TABLE WHERE Lot_Id = @lot_id
				END

				IF ( @cslnt_qty_pull <= @il_qty_balance_existing )
				BEGIN
					SET @final_balance_qty = @il_qty_balance_existing - @cslnt_qty_pull
				END
				ELSE
				BEGIN
					SET @final_balance_qty = 0
				END

				IF ( @il_qty_in_existing - @cslnt_qty_pull > 0 )
				BEGIN
					SET @final_qty_in = @il_qty_in_existing - @cslnt_qty_pull
				END
				ELSE
				BEGIN
					SET @final_qty_in = 0
				END

				UPDATE INVENTORY_LOT SET IL_Qty_In = @final_qty_in , IL_Qty_Balance = @final_balance_qty WHERE Lot_ID = @lot_id
				UPDATE LOT_TABLE SET QTY_IN = @final_qty_in WHERE LOT_ID = @lot_id
			END
		END
	END

	set @il_qty_balance_existing=-1

	select @il_qty_balance_existing = IL_Qty_Balance from Inventory_lot where Lot_Id = @lot_id

	DECLARE @TEMP_PRODUCT_TYPE VARCHAR(50)
	SELECT @TEMP_PRODUCT_TYPE = ISNULL ( P_Type, 'CHIP' ) FROM LOT_TABLE LT, PRODUCT P WHERE LT.Lot_ID = @lot_id AND LT.ProcessStep_ID = P.ProcessStep_ID
	IF ( @TEMP_PRODUCT_TYPE = 'BOARD' OR @TEMP_PRODUCT_TYPE = 'KIT' )
	 BEGIN
		---CLOSE THE LOT ONLY WHEN UNTESTEDQTY = PS SHIPPED QTY + PS ISSUED QTY + REJECTED QTY
		DECLARE @TOT_PS_SHIPPED_ISSUED_QTY INT
		DECLARE @UNTESTED_QTY INT
		DECLARE @REJECTED_QTY INT
		--the following query returns the total shipped and issued qty for the lotid and excludes the qty for the current line item
		SELECT @TOT_PS_SHIPPED_ISSUED_QTY = ISNULL ( SUM(CSLNT.CSLNT_Qty_Pull), 0 )
			FROM Customer_Shipping_Lot_Number_Table CSLNT
			INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID
			INNER JOIN Customer_Shipping_Table CST ON CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID
			INNER JOIN Lot_Table ON CSLNT.Lot_ID = Lot_Table.Lot_ID
			WHERE (Lot_Table.Lot_Entered_By = 0 OR Lot_Table.Lot_Entered_By IS NULL )
			AND (CST.CST_Status = 'ISSUED' OR CST.CST_Status = 'SHIPPED') AND (CSLNT.CSLNT_Qty_Pull > 0)
			AND (CSLNT.Lot_ID = @lot_id) AND CSLIT.CSLIT_ID != @cslit_id

		IF ( @cslit_id != -1 AND ( @is_drop_shipment = 1 OR @is_reduce_inventory = 0 OR @isUpdatedByVendor = 0 ) )
		BEGIN
			SET @TOT_PS_SHIPPED_ISSUED_QTY = ISNULL( @TOT_PS_SHIPPED_ISSUED_QTY, 0) + @cslnt_qty_pull
		END

		SELECT @UNTESTED_QTY = ISNULL ( IL_Test_Qty, 0 ),@REJECTED_QTY = ISNULL ( IL_Rejected_Qty, 0 ) FROM INVENTORY_LOT WHERE Lot_ID = @lot_id

		IF ( @UNTESTED_QTY = ( @TOT_PS_SHIPPED_ISSUED_QTY + @REJECTED_QTY ) )
		BEGIN
			UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @lot_id
		END
		ELSE
		BEGIN
			UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @lot_id
		END
	 END
	 ELSE -- for chip
	 BEGIN
		if @il_qty_balance_existing <= 0
		begin
			IF(@is_confirm = 0)
			BEGIN
					update Lot_Table set lot_current_status = 'Close' where Lot_ID = @lot_id
			END
		end
		else
		begin
			update Lot_Table set lot_current_status = 'Running' where Lot_ID = @lot_id
		end
	END

	IF( @is_confirm = 0 )
	BEGIN
		DECLARE @BOM_PROCESSSTEP_ID INT
		DECLARE @BOM_QUANTITY INT
		DECLARE @BOM_LOT_ID INT
		DECLARE @PROCESSSTEP_ID INT
		DECLARE @LOCATION_ID INT
		DECLARE @BOM_QTY_PULL INT
		SELECT 	@PROCESSSTEP_ID =
			CASE
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PROCESSSTEP_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 AND P.P_TYPE IS NULL THEN  P.PROCESSSTEP_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 AND P.P_TYPE IS NOT NULL THEN ( SELECT PRO.PROCESSSTEP_ID FROM PRODUCT PRO WHERE PRO.PRODUCT_ID = P.P_PARENT_PRODUCT_ID )
			END
		 FROM Product P INNER JOIN Customer_Open_Order_LineItem COOL_1 ON P.Product_ID = COOL_1.Product_ID INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON COOL_1.LineItem_ID = CSLIT.LineItem_ID WHERE CSLIT.CSLIT_ID = @cslit_id 
		
		SELECT @LOCATION_ID = L.LOCATION_ID FROM LOCATION L, VENDOR V, ADDRESS A, CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLT WHERE L.ADDRESS_ID = CST.CST_LOCATION_ADDRESS_ID AND L.VENDOR_ID = V.VENDOR_ID AND L.ADDRESS_ID = A.ADDRESS_ID AND CST.CUSTOMERSHIPPING_ID = CSLT.CUSTOMERSHIPPING_ID AND  L.L_TYPE = 'FINISHED GOODS' AND CSLT.CSLIT_ID = @cslit_id
		DECLARE @bomInventory CURSOR
		SET @bomInventory = CURSOR FOR  
		SELECT bom_processstep_id, qty FROM assembly_bom_setup_table WHERE assembly_processstep_id = @PROCESSSTEP_ID
		OPEN @bomInventory
		FETCH NEXT FROM @bomInventory INTO @BOM_PROCESSSTEP_ID, @BOM_QUANTITY
		WHILE @@FETCH_STATUS = 0
	   	BEGIN
	   			
			SET @IL_QTY_IN_EXISTING = NULL
			SET @IL_QTY_BALANCE_EXISTING = NULL
			SET @FINAL_BALANCE_QTY = NULL
			SET @FINAL_QTY_IN = NULL
			SET @BOM_QTY_PULL = @BOM_QUANTITY * @cslnt_qty_pull

	 	  	SELECT TOP 1 @BOM_LOT_ID = LT.LOT_ID FROM LOT_TABLE LT, INVENTORY_LOT IL WHERE LT.LOT_ID = IL.LOT_ID AND LT.LOT_CURRENT_STATUS = 'RUNNING' AND LT.PROCESSSTEP_ID = @BOM_PROCESSSTEP_ID AND IL.IL_QTY_IN >= @BOM_QTY_PULL AND LT.lOCATION_ID = @LOCATION_ID ORDER BY IL.IL_DATE_IN 
	
			IF( @BOM_LOT_ID IS NOT NULL AND @BOM_QUANTITY > 0 )
			BEGIN
				SELECT @IL_QTY_IN_EXISTING = LT.QTY_IN , @IL_QTY_BALANCE_EXISTING = IL.IL_QTY_BALANCE FROM INVENTORY_LOT IL, LOT_TABLE LT WHERE LT.LOT_ID = @BOM_LOT_ID AND LT.LOT_ID = IL.LOT_ID
		
				IF @IL_QTY_BALANCE_EXISTING IS NULL
				BEGIN
					SELECT @IL_QTY_BALANCE_EXISTING = QTY_IN FROM LOT_TABLE WHERE LOT_ID = @BOM_LOT_ID
				END
		
				IF ( @IL_QTY_BALANCE_EXISTING - @BOM_QTY_PULL > 0)
				BEGIN
					SET @FINAL_BALANCE_QTY = @IL_QTY_BALANCE_EXISTING - @BOM_QTY_PULL		
				END
				ELSE
				BEGIN
					SET @FINAL_BALANCE_QTY = 0			
				END
				
				IF ( @IL_QTY_IN_EXISTING - @BOM_QTY_PULL > 0 )
				BEGIN
					SET @FINAL_QTY_IN = @IL_QTY_IN_EXISTING - @BOM_QTY_PULL
				END
				ELSE
				BEGIN
					SET @FINAL_QTY_IN = 0			
				END
	
				UPDATE INVENTORY_LOT SET IL_QTY_IN = @FINAL_QTY_IN , IL_QTY_BALANCE = @FINAL_BALANCE_QTY WHERE LOT_ID = @BOM_LOT_ID
				UPDATE LOT_TABLE SET QTY_IN = @FINAL_QTY_IN WHERE LOT_ID = @BOM_LOT_ID
			END
	
			SET @BOM_PROCESSSTEP_ID = NULL
			SET @BOM_QUANTITY =  NULL		
			SET @IL_QTY_IN_EXISTING = NULL
			SET @IL_QTY_BALANCE_EXISTING = NULL
			SET @FINAL_BALANCE_QTY = NULL
			SET @FINAL_QTY_IN = NULL
			SET @BOM_QTY_PULL = NULL
			SET @BOM_LOT_ID = NULL
	
			FETCH NEXT FROM @bomInventory INTO @BOM_PROCESSSTEP_ID, @BOM_QUANTITY
	 	END
	END	
end
